<!doctype html>
<html lang="en">
<head>
	<meta charset="UTF-8">
	<title>distence</title>
</head>
<body>
	<?php 

    $zip=63939;
    echo '<h1>距离'.$zip.'最近的商店有：</h1>';
    $dbc=mysqli_connect('localhost','root','root','zips');
    $q="SELECT latitude,longitute From zip_codes where zip_code='$zip'AND latitude IS NOT NULL";
    $r=mysqli_query($dbc,$q);
    if(mysqli_num_rows($r)==1){

    	list($lat,$long)=mysqli_fetch_array($r,MYSQLI_NUM);
    	$q="SELECT name,CONCAT_WS('<br>',address1,address2),city,state,stroes.zip_code,phone,ROUND(DEGREES(ACOS(SIN(RADIANS($lat))*SIN(RADIANS(latitude))+COS(RADIANS($lat))*COS(RADIANS(latitude))*COS(RADIANS($long-longitude))))*69.09)AS distance From stroes LEFT JOIN zip_codes USING(zip_code) ORDER BY distance ASC ";
    	$r=mysqli_query($dbc,$q);
    	if(mysqli_num_rows($r)>0){

    		while($rows=mysqli_fetch_array($r,MYSQLI_NUM)){

    			echo '<h2> $rows[0]</h2>
    			<p> $rows[1]<br>'.ucfirst(strtolower($rows[2])).',$rows[3] $rows[4]<br>rows[5]<br>(approximately $rows[6] miles)</p>';
    		}
    	}else{

    		echo'<h3>查无此地！</h3>';
    	}
    }else{
    		echo "邮政编码有误！";

    }
    	mysqli_close($dbc);

	 ?>
</body>
</html>